IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CloneClinicalTrialBudgetVisitsStaffTasksExpenses]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CloneClinicalTrialBudgetVisitsStaffTasksExpenses]
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[CloneClinicalTrialBudgetVisitsStaffTasksExpenses]
(
  @budgetId int,
  @budgetIdNew int
) 
AS 
BEGIN

--VISITS
--------------------------
DECLARE @budgetVisitId INT
  INSERT INTO ClinicalTrialBudgetVisit  (ClinicalTrialBudgetId, VisitNumber, VisitDescription)
		SELECT @budgetIdNew, VisitNumber, VisitDescription FROM ClinicalTrialBudgetVisit ct  WHERE ClinicalTrialBudgetId = @budgetId
   SELECT @budgetVisitId =  IDENT_CURRENT( 'ClinicalTrialBudgetVisit' )
---------------------------

--STAFF
----------------------------

DECLARE @budgetstaffId int
INSERT INTO ClinicalTrialBudgetStaff (ClinicalTrialBudgetId, StudyStaffRoleCode, OtherRoleText, Rate) SELECT @budgetIdNew, StudyStaffRoleCode, OtherRoleText, Rate FROM clinicaltrialbudgetstaff where ClinicalTrialBudgetId = @budgetId
SELECT @budgetstaffId =  IDENT_CURRENT( 'ClinicalTrialBudgetTask' )
-----------------------------

--TASKS
-----------------------------
DECLARE @budgetTaskId int
			INSERT INTO ClinicalTrialBudgetTask (ClinicalTrialBudgetId, ClinicalTrialBudgetStaffId, BudgetTaskCode,
													Hours, OtherTaskText, TaskCost, ApplyOverhead, OverRideRate) SELECT @budgetIdNew, ClinicalTrialBudgetStaffId, BudgetTaskCode,
													Hours, OtherTaskText, TaskCost, ApplyOverhead, OverRideRate FROM clinicaltrialbudgettask where ClinicalTrialBudgetId = @budgetId
SELECT @budgetTaskId =  IDENT_CURRENT( 'ClinicalTrialBudgetTask' )
-----------------------------

--EXPENSES
-----------------------------
INSERT INTO ClinicalTrialBudgetExpense (ClinicalTrialBudgetId, StudyCostCode, OtherExpenseText, ExpenseCost, VariableCost, ApplyOverhead) SELECT @budgetIdNew, StudyCostCode, OtherExpenseText, ExpenseCost, VariableCost, ApplyOverhead FROM clinicaltrialbudgetexpense where ClinicalTrialBudgetId = @budgetId
----------------------------

select * from ClinicalTrialBudgetVisit vs join ClinicalTrialBudgetVisit old on old.VisitNumber = vs.VisitNumber where vs.ClinicalTrialBudgetId = @budgetIdNew and old.ClinicalTrialBudgetId = @budgetId

END
 
